var mysql = require('mysql')
// var models = require('./db')
const express = require('express')
const app = express();
var db=require("./sql.js"); //引用数据库连接配置

// const sql6 ="SELECT * FROM `test01`.`sheet1` WHERE `酒店直销拒单率` <> 'NULL' AND `酒店直销拒单率` <> '0' ORDER BY  `省份`,`城市`,`城市直销拒单率` DESC limit 5";
// db.connect(sql6,function(error,data){
//   // res.render('theResult', { info:data });
//   // res.json(data);
//   // var data2 = json(data);
//   console.log( typeof data2,'data\n',data2);
// });

// 数据答复接口
const sql2 ="SELECT * FROM `test01`.`sheet1` WHERE `酒店直销拒单率` <> 'NULL' AND `酒店直销拒单率` <> '0' ORDER BY  `省份`,`城市`,`城市直销拒单率` DESC limit 5";
app.get('/get',(req,res,next) => {
  db.connect(sql2,function(error,data){
    // res.render('theResult', { info:data });
    res.json(data);
    // var target = JSON.parse(data);
    // console.log(typeof );
    // console.log( typeof data,'data',typeof res , 'res');
  });
  // res.json({
  //   data: '后台返回结果'
  // })
})


//结果统计表
const sql4 ="SELECT cityname,count(cityname) as times from redata GROUP BY cityname;";
app.get('/getb',(req,res,next) => {
  db.connect(sql4,function(error,data){
		// res.render('theResult', { info:data });
		res.json(data);
	});

})
//原结果
const sql3 ="select a.* from luckycoffee_copy1 a inner join (select cityname,max(review_count) as review_count from luckycoffee_copy1 group by cityname) b on a.cityname=b.cityname and a.review_count=b.review_count order by a.cityname;";
app.get('/getc',(req,res,next) => {
  db.connect(sql3,function(error,data){
		// res.render('theResult', { info:data });
		res.json(data);
	});
  
})
// var http = require('http').createServer(app);
var outPort = 19532;
app.listen(outPort);//监听端口
console.log('success listen at',outPort);
//使用 'node 文件名' 在cmd启动
// var conn = mysql.createConnection(models.mysql)

// conn.connect()

